﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ColorsController
/// </summary>
public class ColorsController:Interface
{
    private SqlConnection con;
	public ColorsController()
	{
	    con = ConnectDB.Connect();
        ConnectDB.Open();	    
	}

    public int Insert(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Color";
            cmd.CommandType = CommandType.StoredProcedure;
            Colors colors = (Colors)obj;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = colors.Name;
            cmd.Parameters.Add("@value", SqlDbType.NVarChar, 50).Value = colors.Value;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = colors.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = colors.Status;
            cmd.Parameters.Add("@dateStart", SqlDbType.DateTime).Value = colors.DateStart;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Update(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update_Color";
            cmd.CommandType = CommandType.StoredProcedure;
            Colors colors = (Colors)obj;
            cmd.Parameters.Add("@color_id", SqlDbType.Int).Value = colors.Color_id;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = colors.Name;
            cmd.Parameters.Add("@value", SqlDbType.NVarChar, 50).Value = colors.Value;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = colors.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = colors.Status;
            cmd.Parameters.Add("@dateUpdate", SqlDbType.DateTime).Value = colors.DateUpdate;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Delete(int id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete_Color";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@color_id", SqlDbType.Int).Value = id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public DataTable GetAll()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Color WHERE [status]=1 ORDER BY [order] ASC, dateStart DESC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }

    public DataTable GetByID(int id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Color WHERE color_id=@id AND [status]=1 ORDER BY [order] ASC, dateStart DESC";
            cmd.Parameters.AddWithValue("@id", id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public DataTable GetByProductID(int product_id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT c.* FROM Color AS c INNER JOIN Product_Color AS pc ON c.color_id=pc.color_id INNER JOIN Product AS p ON pc.product_id=p.product_id WHERE p.product_id=@product_id AND c.[status]=1 ORDER BY c.[order]  ASC, c.dateStart DESC";
            cmd.Parameters.AddWithValue("@product_id", product_id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }

    public DataTable GetByProductId(int productId)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            string select = "select Color.name from Product_Color inner join Color ";
                   select+="on Product_Color.color_id=Color.color_id inner join Product on Product_Color.product_id";
                   select+="=Product.product_id AND Product.product_id=@id";
            cmd.CommandText = select;
            cmd.Parameters.AddWithValue("@id", productId);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {


            throw;
        }
    }
}